package com.example.service;

import com.example.model.DbConnection;
import com.example.util.RedisCacheUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.cache.annotation.Cacheable;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;
import com.example.model.TableInfo;
import com.example.model.ColumnInfo;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Async;
import java.util.ArrayList;
import com.example.model.ForeignKeyInfo;
import java.util.HashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.Random;

@Service
@Slf4j
public class MetadataService {
    @Autowired
    private JdbcTemplate defaultJdbcTemplate;

    @Autowired
    private DbConnectionService dbConnectionService;
    
    @Autowired
    private SchemaMetadataCache metadataCache;
    
    @Autowired
    private RedisCacheUtil redisCacheUtil;
    
    @Autowired
    private LockService lockService;
    
    @Autowired
    private CacheMetricsCollector cacheMetricsCollector;

    @Value("${spring.datasource.name:sql_translator}")
    private String defaultDatabaseName;
    
    @Value("${cache.metadata.expireTime:86400}")
    private long metadataCacheExpireTime;

    // 缓存JdbcTemplate实例，避免频繁创建
    private final Map<Long, JdbcTemplate> jdbcTemplateCache = new ConcurrentHashMap<>();
    
    // 元数据缓存前缀
    private static final String METADATA_CACHE_PREFIX = "metadata:";

    /**
     * 获取默认数据库的结构描述
     */
    public String getDefaultSchemaDescription() {
        String cacheKey = METADATA_CACHE_PREFIX + "default";
        
        // 首先尝试从Redis缓存获取
        Object cachedData = redisCacheUtil.get(cacheKey);
        
        if (cachedData != null) {
            log.debug("从Redis缓存获取默认数据库元数据");
            return (String) cachedData;
        }
        
        // Redis缓存未命中，尝试从内存缓存获取
        SchemaMetadataCache.DatabaseMetadata metadata = metadataCache.getMetadata("default");
        
        if (metadata != null && !metadata.isExpired()) {
            // 内存缓存命中，更新访问信息
            metadata.accessed();
            log.debug("从内存缓存获取默认数据库元数据, 缓存年龄: {}秒, 访问次数: {}", 
                    metadata.getAgeInSeconds(), metadata.getAccessCount());
            
            String result = formatSchemaDescription(metadata.getTables());
            
            // 同步到Redis缓存
            redisCacheUtil.set(cacheKey, result, metadataCacheExpireTime, TimeUnit.SECONDS);
            
            return result;
        }
        
        // 缓存未命中或已过期，使用分布式锁获取最新数据
        boolean lockAcquired = lockService.tryMetadataLock("default", () -> {
            try {
                log.info("从数据库获取默认数据库元数据");
                List<TableInfo> tables = getAllTables(defaultJdbcTemplate, defaultDatabaseName);
                
                // 更新内存缓存
                metadataCache.cacheMetadata("default", tables);
                
                // 格式化描述
                String description = formatSchemaDescription(tables);
                
                // 更新Redis缓存
                redisCacheUtil.set(cacheKey, description, metadataCacheExpireTime, TimeUnit.SECONDS);
            } catch (Exception e) {
                log.error("获取默认数据库结构失败", e);
                
                // 如果有过期缓存，则使用过期缓存
                if (metadata != null) {
                    log.warn("使用过期缓存作为降级策略");
                    String description = formatSchemaDescription(metadata.getTables());
                    redisCacheUtil.set(cacheKey, description, metadataCacheExpireTime, TimeUnit.SECONDS);
                } else {
                    // 设置一个错误消息
                    redisCacheUtil.set(cacheKey, "数据库结构获取失败, 请稍后重试", 60, TimeUnit.SECONDS);
                }
            }
        });
        
        // 检查缓存是否设置成功
        Object result = redisCacheUtil.get(cacheKey);
        if (result != null) {
            return (String) result;
        } else {
            return "获取数据库元数据失败，请重试";
        }
    }

    /**
     * 获取指定数据库连接的结构描述
     */
    public String getSchemaDescription(Long connectionId, Long userId) {
        String cacheKey = METADATA_CACHE_PREFIX + connectionId;
        long startTime = System.currentTimeMillis();
        boolean cacheHit = false;
        
        try {
            // 首先尝试从Redis缓存获取
            Object cachedData = redisCacheUtil.get(cacheKey);
            
            if (cachedData != null) {
                log.debug("从Redis缓存获取指定数据库元数据, 连接ID: {}", connectionId);
                cacheHit = true;
                return (String) cachedData;
            }
            
            // Redis缓存未命中，尝试从内存缓存获取
            SchemaMetadataCache.DatabaseMetadata metadata = metadataCache.getMetadata(connectionId.toString());
            
            if (metadata != null && !metadata.isExpired()) {
                // 内存缓存命中，更新访问信息
                metadata.accessed();
                log.debug("从内存缓存获取指定数据库元数据, 连接ID: {}, 缓存年龄: {}秒", 
                        connectionId, metadata.getAgeInSeconds());
                
                String result = formatSchemaDescription(metadata.getTables());
                
                // 同步到Redis缓存
                redisCacheUtil.set(cacheKey, result, metadataCacheExpireTime, TimeUnit.SECONDS);
                
                cacheHit = true;
                return result;
            }
            
            // 缓存未命中或已过期，使用分布式锁获取最新数据
            boolean lockAcquired = lockService.tryMetadataLock(connectionId.toString(), () -> {
                try {
                    DbConnection connection = dbConnectionService.getConnectionById(connectionId, userId);
                    log.info("从数据库获取指定数据库元数据, 连接ID: {}, 数据库: {}", 
                            connectionId, connection.getDatabaseName());
                    
                    JdbcTemplate jdbcTemplate = getJdbcTemplate(connection);
                    List<TableInfo> tables = getAllTables(jdbcTemplate, connection.getDatabaseName());
                    
                    // 更新内存缓存
                    metadataCache.cacheMetadata(connectionId.toString(), tables);
                    
                    // 格式化描述
                    String description = formatSchemaDescription(tables);
                    
                    // 更新Redis缓存
                    redisCacheUtil.set(cacheKey, description, metadataCacheExpireTime, TimeUnit.SECONDS);
                } catch (Exception e) {
                    log.error("获取数据库结构失败: connectionId={}", connectionId, e);
                    
                    // 如果有过期缓存，则使用过期缓存
                    if (metadata != null) {
                        log.warn("使用过期缓存作为降级策略, 连接ID: {}", connectionId);
                        String description = formatSchemaDescription(metadata.getTables());
                        redisCacheUtil.set(cacheKey, description, metadataCacheExpireTime, TimeUnit.SECONDS);
                    } else {
                        // 设置一个错误消息
                        redisCacheUtil.set(cacheKey, "数据库结构获取失败, 请稍后重试", 60, TimeUnit.SECONDS);
                    }
                }
            });

            // 检查缓存是否设置成功
            Object result = redisCacheUtil.get(cacheKey);
            if (result != null) {
                return (String) result;
            } else {
                return "获取数据库元数据失败，请重试";
            }
        } finally {
            // 记录缓存访问统计
            long loadTime = System.currentTimeMillis() - startTime;
            cacheMetricsCollector.recordCacheAccess(connectionId, cacheHit, loadTime);
        }
    }

    /**
     * 刷新默认数据库元数据缓存
     */
    @Async
    public void refreshDefaultSchemaMetadata() {
        // 使用分布式锁避免重复刷新
        lockService.tryMetadataLock("default_refresh", () -> {
            try {
                log.info("开始刷新默认数据库元数据缓存");
                List<TableInfo> tables = getAllTables(defaultJdbcTemplate, defaultDatabaseName);
                
                // 更新内存缓存
                metadataCache.cacheMetadata("default", tables);
                
                // 更新Redis缓存
                String cacheKey = METADATA_CACHE_PREFIX + "default";
                String description = formatSchemaDescription(tables);
                redisCacheUtil.set(cacheKey, description, metadataCacheExpireTime, TimeUnit.SECONDS);
                
                log.info("刷新默认数据库元数据缓存完成, 共{}张表", tables.size());
            } catch (Exception e) {
                log.error("刷新默认数据库元数据缓存失败", e);
            }
        });
    }
    
    /**
     * 刷新指定连接的元数据缓存
     */
    @Async
    public void refreshConnectionMetadata(Long connectionId, Long userId) {
        // 使用分布式锁避免重复刷新
        lockService.tryMetadataLock("refresh_" + connectionId, () -> {
            try {
                log.info("开始刷新指定数据库元数据缓存, 连接ID: {}", connectionId);
                
                DbConnection connection = dbConnectionService.getConnectionById(connectionId, userId);
                JdbcTemplate jdbcTemplate = getJdbcTemplate(connection);
                List<TableInfo> tables = getAllTables(jdbcTemplate, connection.getDatabaseName());
                
                // 更新内存缓存
                metadataCache.cacheMetadata(connectionId.toString(), tables);
                
                // 更新Redis缓存
                String cacheKey = METADATA_CACHE_PREFIX + connectionId;
                String description = formatSchemaDescription(tables);
                redisCacheUtil.set(cacheKey, description, metadataCacheExpireTime, TimeUnit.SECONDS);
                
                log.info("刷新指定数据库元数据缓存完成, 连接ID: {}, 共{}张表", connectionId, tables.size());
            } catch (Exception e) {
                log.error("刷新指定数据库元数据缓存失败, 连接ID: {}", connectionId, e);
            }
        });
    }
    
    /**
     * 清理过期的元数据缓存
     */
    public void cleanExpiredMetadata() {
        // 使用分布式锁确保只有一个实例执行清理
        lockService.tryMetadataLock("clean_expired", () -> {
            // 清理内存缓存
            metadataCache.clearAll();
            jdbcTemplateCache.clear();
            
            // 不清理Redis缓存，让其自动过期
            
            log.info("清理所有元数据和JDBC模板缓存");
        });
    }

    /**
     * 清除指定连接的缓存
     */
    public void clearSchemaCache(Long connectionId) {
        // 使用分布式锁确保清理操作的一致性
        lockService.tryMetadataLock("clear_" + connectionId, () -> {
            // 清理内存缓存
            metadataCache.removeMetadata(connectionId.toString());
            jdbcTemplateCache.remove(connectionId);
            
            // 清理Redis缓存
            String cacheKey = METADATA_CACHE_PREFIX + connectionId;
            redisCacheUtil.delete(cacheKey);
            
            log.info("清除数据库结构缓存: connectionId={}", connectionId);
        });
    }

    /**
     * 获取JdbcTemplate实例
     */
    public JdbcTemplate getJdbcTemplate(DbConnection connection) {
        return jdbcTemplateCache.computeIfAbsent(connection.getId(), id -> {
            DataSource dataSource = createDataSource(connection);
            return new JdbcTemplate(dataSource);
        });
    }

    /**
     * 创建DataSource
     */
    private DataSource createDataSource(DbConnection connection) {
        String jdbcUrl = dbConnectionService.buildJdbcUrl(connection);
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(connection.getUsername());
        dataSource.setPassword(connection.getPassword());
        
        // 设置驱动类
        String driverClassName;
        switch (connection.getDbType().toLowerCase()) {
            case "mysql":
                driverClassName = "com.mysql.cj.jdbc.Driver";
                break;
            case "postgresql":
                driverClassName = "org.postgresql.Driver";
                break;
            case "oracle":
                driverClassName = "oracle.jdbc.OracleDriver";
                break;
            case "sqlserver":
                driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                break;
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + connection.getDbType());
        }
        dataSource.setDriverClassName(driverClassName);
        
        return dataSource;
    }

    /**
     * 获取数据库所有表及列信息
     */
    private List<TableInfo> getAllTables(JdbcTemplate jdbcTemplate, String databaseName) {
        String sql = "SELECT " +
                "table_name, " +
                "table_comment " +
                "FROM information_schema.tables " +
                "WHERE table_schema = ? " +
                "AND table_type = 'BASE TABLE'";

        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            String tableName = rs.getString("table_name");
            String tableComment = rs.getString("table_comment");
            
            // 获取列信息(包含主键标识)
            List<ColumnInfo> columns = getTableColumns(jdbcTemplate, databaseName, tableName);
            
            // 初始化表信息对象
            TableInfo tableInfo = new TableInfo(tableName, tableComment, columns);
            
            // 获取主键信息
            List<String> primaryKeys = getTablePrimaryKeys(jdbcTemplate, databaseName, tableName);
            tableInfo.setPrimaryKeys(primaryKeys);
            
            // 标记主键列
            for (ColumnInfo column : columns) {
                if (primaryKeys.contains(column.getName())) {
                    column.setPrimaryKey(true);
                }
            }
            
            // 获取外键信息
            List<ForeignKeyInfo> foreignKeys = getTableForeignKeys(jdbcTemplate, databaseName, tableName);
            tableInfo.setForeignKeys(foreignKeys);
            
            return tableInfo;
        }, databaseName);
    }

    /**
     * 获取表的所有列信息，针对不同数据库类型采用不同的查询方式
     */
    private List<ColumnInfo> getTableColumns(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String dbType = detectDatabaseType(jdbcTemplate);
        
        try {
            switch (dbType) {
                case "MYSQL":
                    return getMySqlColumns(jdbcTemplate, databaseName, tableName);
                case "POSTGRESQL":
                    return getPostgreSqlColumns(jdbcTemplate, databaseName, tableName);
                case "ORACLE":
                    return getOracleColumns(jdbcTemplate, databaseName, tableName);
                case "SQLSERVER":
                    return getSqlServerColumns(jdbcTemplate, databaseName, tableName);
                default:
                    // 默认使用MySQL的查询方式
                    return getMySqlColumns(jdbcTemplate, databaseName, tableName);
            }
        } catch (Exception e) {
            log.warn("获取表列信息失败: {}.{}, 尝试使用通用方式", databaseName, tableName, e);
            return getGenericColumns(jdbcTemplate, databaseName, tableName);
        }
    }

    /**
     * 检测数据库类型
     */
    private String detectDatabaseType(JdbcTemplate jdbcTemplate) {
        try {
            String dbProductName = jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName().toUpperCase();
            if (dbProductName.contains("MYSQL")) {
                return "MYSQL";
            } else if (dbProductName.contains("POSTGRESQL")) {
                return "POSTGRESQL";
            } else if (dbProductName.contains("ORACLE")) {
                return "ORACLE";
            } else if (dbProductName.contains("MICROSOFT") || dbProductName.contains("SQL SERVER")) {
                return "SQLSERVER";
            } else {
                return "UNKNOWN";
            }
        } catch (Exception e) {
            log.warn("检测数据库类型失败，默认使用MySQL", e);
            return "MYSQL";
        }
    }

    /**
     * MySQL数据库列信息查询
     */
    private List<ColumnInfo> getMySqlColumns(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "column_name, " +
                "data_type, " +
                "column_comment, " +
                "column_key " +
                "FROM information_schema.columns " +
                "WHERE table_schema = ? " +
                "AND table_name = ? " +
                "ORDER BY ordinal_position";

        return jdbcTemplate.query(sql, 
            (rs, rowNum) -> {
                String columnName = rs.getString("column_name");
                String dataType = rs.getString("data_type");
                String comment = rs.getString("column_comment");
                String columnKey = rs.getString("column_key");
                
                boolean isPrimaryKey = "PRI".equals(columnKey);
                
                return new ColumnInfo(columnName, dataType, comment, isPrimaryKey);
            },
            databaseName, tableName
        );
    }

    /**
     * PostgreSQL数据库列信息查询
     */
    private List<ColumnInfo> getPostgreSqlColumns(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "a.attname as column_name, " +
                "format_type(a.atttypid, a.atttypmod) as data_type, " +
                "col_description(a.attrelid, a.attnum) as column_comment, " +
                "CASE WHEN p.contype = 'p' AND a.attnum = ANY(p.conkey) THEN true ELSE false END as is_primary_key " +
                "FROM pg_attribute a " +
                "LEFT JOIN pg_constraint p ON p.conrelid = a.attrelid AND p.contype = 'p' " +
                "WHERE a.attrelid = ?::regclass::oid " +
                "AND a.attnum > 0 " +
                "AND NOT a.attisdropped " +
                "ORDER BY a.attnum";

        return jdbcTemplate.query(sql, 
            (rs, rowNum) -> new ColumnInfo(
                rs.getString("column_name"),
                rs.getString("data_type"),
                rs.getString("column_comment"),
                rs.getBoolean("is_primary_key")
            ),
            tableName
        );
    }

    /**
     * Oracle数据库列信息查询
     */
    private List<ColumnInfo> getOracleColumns(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        // 查询列信息
        String sql = "SELECT " +
                "c.COLUMN_NAME, " +
                "c.DATA_TYPE, " +
                "c.COMMENTS, " +
                "CASE WHEN p.CONSTRAINT_TYPE = 'P' THEN 1 ELSE 0 END AS IS_PRIMARY_KEY " +
                "FROM ALL_TAB_COLUMNS tc " +
                "JOIN ALL_COL_COMMENTS c ON tc.TABLE_NAME = c.TABLE_NAME AND tc.COLUMN_NAME = c.COLUMN_NAME " +
                "LEFT JOIN (SELECT ucc.TABLE_NAME, ucc.COLUMN_NAME, uc.CONSTRAINT_TYPE " +
                "          FROM ALL_CONSTRAINTS uc, ALL_CONS_COLUMNS ucc " +
                "          WHERE uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME " +
                "          AND uc.CONSTRAINT_TYPE = 'P') p " +
                "ON tc.TABLE_NAME = p.TABLE_NAME AND tc.COLUMN_NAME = p.COLUMN_NAME " +
                "WHERE tc.TABLE_NAME = ? " +
                "ORDER BY tc.COLUMN_ID";

        return jdbcTemplate.query(sql, 
            (rs, rowNum) -> new ColumnInfo(
                rs.getString("COLUMN_NAME"),
                rs.getString("DATA_TYPE"),
                rs.getString("COMMENTS"),
                rs.getInt("IS_PRIMARY_KEY") == 1
            ),
            tableName.toUpperCase()
        );
    }

    /**
     * SQL Server数据库列信息查询
     */
    private List<ColumnInfo> getSqlServerColumns(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "c.name AS column_name, " +
                "t.name AS data_type, " +
                "CAST(ep.value AS NVARCHAR(4000)) AS column_comment, " +
                "CASE WHEN pk.is_primary_key = 1 THEN 1 ELSE 0 END AS is_primary_key " +
                "FROM sys.columns c " +
                "JOIN sys.types t ON c.user_type_id = t.user_type_id " +
                "JOIN sys.tables tb ON c.object_id = tb.object_id " +
                "LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description' " +
                "LEFT JOIN (SELECT ic.object_id, ic.column_id, 1 AS is_primary_key " +
                "           FROM sys.index_columns ic " +
                "           JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id " +
                "           WHERE i.is_primary_key = 1) pk " +
                "ON c.object_id = pk.object_id AND c.column_id = pk.column_id " +
                "WHERE tb.name = ? " +
                "ORDER BY c.column_id";

        return jdbcTemplate.query(sql, 
            (rs, rowNum) -> new ColumnInfo(
                rs.getString("column_name"),
                rs.getString("data_type"),
                rs.getString("column_comment"),
                rs.getInt("is_primary_key") == 1
            ),
            tableName
        );
    }

    /**
     * 通用列信息查询（作为备用方案）
     */
    private List<ColumnInfo> getGenericColumns(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "column_name, " +
                "data_type, " +
                "column_comment " +
                "FROM information_schema.columns " +
                "WHERE table_schema = ? " +
                "AND table_name = ? " +
                "ORDER BY ordinal_position";

        return jdbcTemplate.query(sql, 
            (rs, rowNum) -> new ColumnInfo(
                rs.getString("column_name"),
                rs.getString("data_type"),
                rs.getString("column_comment")
            ),
            databaseName, tableName
        );
    }

    /**
     * 获取表的主键列，针对不同数据库类型采用不同的查询方式
     */
    private List<String> getTablePrimaryKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String dbType = detectDatabaseType(jdbcTemplate);
        
        try {
            switch (dbType) {
                case "MYSQL":
                    return getMySqlPrimaryKeys(jdbcTemplate, databaseName, tableName);
                case "POSTGRESQL":
                    return getPostgreSqlPrimaryKeys(jdbcTemplate, databaseName, tableName);
                case "ORACLE":
                    return getOraclePrimaryKeys(jdbcTemplate, databaseName, tableName);
                case "SQLSERVER":
                    return getSqlServerPrimaryKeys(jdbcTemplate, databaseName, tableName);
                default:
                    return getMySqlPrimaryKeys(jdbcTemplate, databaseName, tableName);
            }
        } catch (Exception e) {
            log.warn("获取表主键信息失败: {}.{}, 原因: {}", databaseName, tableName, e.getMessage());
            return new ArrayList<>();
        }
    }

    /**
     * MySQL主键查询
     */
    private List<String> getMySqlPrimaryKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT k.COLUMN_NAME " +
                "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t " +
                "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k " +
                "USING(CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME) " +
                "WHERE t.CONSTRAINT_TYPE='PRIMARY KEY' " +
                "AND t.TABLE_SCHEMA=? " +
                "AND t.TABLE_NAME=? " +
                "ORDER BY k.ORDINAL_POSITION";

        return jdbcTemplate.queryForList(sql, String.class, databaseName, tableName);
    }

    /**
     * PostgreSQL主键查询
     */
    private List<String> getPostgreSqlPrimaryKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT a.attname as column_name " +
                "FROM pg_index i " +
                "JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) " +
                "WHERE i.indrelid = ?::regclass " +
                "AND i.indisprimary";

        return jdbcTemplate.queryForList(sql, String.class, tableName);
    }

    /**
     * Oracle主键查询
     */
    private List<String> getOraclePrimaryKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT cols.column_name " +
                "FROM all_constraints cons, all_cons_columns cols " +
                "WHERE cols.table_name = ? " +
                "AND cons.constraint_type = 'P' " +
                "AND cons.constraint_name = cols.constraint_name " +
                "AND cons.owner = cols.owner " +
                "ORDER BY cols.position";

        return jdbcTemplate.queryForList(sql, String.class, tableName.toUpperCase());
    }

    /**
     * SQL Server主键查询
     */
    private List<String> getSqlServerPrimaryKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT c.name AS column_name " +
                "FROM sys.indexes i " +
                "JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id " +
                "JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id " +
                "JOIN sys.tables t ON i.object_id = t.object_id " +
                "WHERE i.is_primary_key = 1 " +
                "AND t.name = ? " +
                "ORDER BY ic.key_ordinal";

        return jdbcTemplate.queryForList(sql, String.class, tableName);
    }

    /**
     * 获取表的外键关系，针对不同数据库类型采用不同的查询方式
     */
    private List<ForeignKeyInfo> getTableForeignKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String dbType = detectDatabaseType(jdbcTemplate);
        
        try {
            switch (dbType) {
                case "MYSQL":
                    return getMySqlForeignKeys(jdbcTemplate, databaseName, tableName);
                case "POSTGRESQL":
                    return getPostgreSqlForeignKeys(jdbcTemplate, databaseName, tableName);
                case "ORACLE":
                    return getOracleForeignKeys(jdbcTemplate, databaseName, tableName);
                case "SQLSERVER":
                    return getSqlServerForeignKeys(jdbcTemplate, databaseName, tableName);
                default:
                    return getMySqlForeignKeys(jdbcTemplate, databaseName, tableName);
            }
        } catch (Exception e) {
            log.warn("获取表外键信息失败: {}.{}, 原因: {}", databaseName, tableName, e.getMessage());
            return new ArrayList<>();
        }
    }

    /**
     * MySQL外键查询
     */
    private List<ForeignKeyInfo> getMySqlForeignKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "k.COLUMN_NAME, " +
                "k.REFERENCED_TABLE_NAME, " +
                "k.REFERENCED_COLUMN_NAME, " +
                "k.CONSTRAINT_NAME " +
                "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k " +
                "WHERE k.TABLE_SCHEMA=? " +
                "AND k.TABLE_NAME=? " +
                "AND k.REFERENCED_TABLE_NAME IS NOT NULL";

        return jdbcTemplate.query(sql, (rs, rowNum) -> new ForeignKeyInfo(
                rs.getString("COLUMN_NAME"),
                rs.getString("REFERENCED_TABLE_NAME"),
                rs.getString("REFERENCED_COLUMN_NAME"),
                rs.getString("CONSTRAINT_NAME")
        ), databaseName, tableName);
    }

    /**
     * PostgreSQL外键查询
     */
    private List<ForeignKeyInfo> getPostgreSqlForeignKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "kcu.column_name, " +
                "ccu.table_name AS referenced_table_name, " +
                "ccu.column_name AS referenced_column_name, " +
                "tc.constraint_name " +
                "FROM information_schema.table_constraints AS tc " +
                "JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name " +
                "JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name " +
                "WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name=?";

        return jdbcTemplate.query(sql, (rs, rowNum) -> new ForeignKeyInfo(
                rs.getString("column_name"),
                rs.getString("referenced_table_name"),
                rs.getString("referenced_column_name"),
                rs.getString("constraint_name")
        ), tableName);
    }

    /**
     * Oracle外键查询
     */
    private List<ForeignKeyInfo> getOracleForeignKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "a.column_name, " +
                "c_pk.table_name as referenced_table_name, " +
                "a_pk.column_name as referenced_column_name, " +
                "c.constraint_name " +
                "FROM all_cons_columns a " +
                "JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name " +
                "JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name " +
                "JOIN all_cons_columns a_pk ON c_pk.owner = a_pk.owner AND c_pk.constraint_name = a_pk.constraint_name " +
                "WHERE c.constraint_type = 'R' " +
                "AND a.table_name = ? " +
                "ORDER BY a.position";

        return jdbcTemplate.query(sql, (rs, rowNum) -> new ForeignKeyInfo(
                rs.getString("column_name"),
                rs.getString("referenced_table_name"),
                rs.getString("referenced_column_name"),
                rs.getString("constraint_name")
        ), tableName.toUpperCase());
    }

    /**
     * SQL Server外键查询
     */
    private List<ForeignKeyInfo> getSqlServerForeignKeys(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT " +
                "COL_NAME(fc.parent_object_id, fc.parent_column_id) AS column_name, " +
                "OBJECT_NAME(fc.referenced_object_id) AS referenced_table_name, " +
                "COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name, " +
                "f.name AS constraint_name " +
                "FROM sys.foreign_keys AS f " +
                "INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id " +
                "INNER JOIN sys.tables AS t ON t.object_id = fc.parent_object_id " +
                "WHERE t.name = ?";

        return jdbcTemplate.query(sql, (rs, rowNum) -> new ForeignKeyInfo(
                rs.getString("column_name"),
                rs.getString("referenced_table_name"),
                rs.getString("referenced_column_name"),
                rs.getString("constraint_name")
        ), tableName);
    }

    /**
     * 格式化数据库结构描述
     */
    private String formatSchemaDescription(List<TableInfo> tables) {
        StringBuilder sb = new StringBuilder("数据库包含以下表结构：\n\n");
        
        for (TableInfo table : tables) {
            // 添加表信息
            sb.append(String.format("表名：%s", table.getName()));
            if (table.getComment() != null && !table.getComment().isEmpty()) {
                sb.append(String.format("（%s）", table.getComment()));
            }
            sb.append("\n");
            
            // 添加主键信息
            if (!table.getPrimaryKeys().isEmpty()) {
                sb.append("主键：");
                sb.append(String.join(", ", table.getPrimaryKeys()));
                sb.append("\n");
            }
            
            // 添加字段信息
            sb.append("字段列表：\n");
            for (ColumnInfo column : table.getColumns()) {
                sb.append(String.format("- %s (%s)", column.getName(), column.getType()));
                
                // 标记主键
                if (column.isPrimaryKey()) {
                    sb.append(" [主键]");
                }
                
                if (column.getComment() != null && !column.getComment().isEmpty()) {
                    sb.append(String.format(" %s", column.getComment()));
                }
                sb.append("\n");
            }
            
            // 添加外键信息
            if (!table.getForeignKeys().isEmpty()) {
                sb.append("\n外键关系：\n");
                for (ForeignKeyInfo fk : table.getForeignKeys()) {
                    sb.append(String.format("- %s -> %s.%s\n", 
                            fk.getColumnName(), 
                            fk.getReferencedTable(), 
                            fk.getReferencedColumn()));
                }
            }
            
            sb.append("\n");
        }
        
        // 添加全局关系图描述
        sb.append("\n表间关系：\n");
        Map<String, List<String>> tableRelations = new HashMap<>();
        
        // 构建表关系图
        for (TableInfo table : tables) {
            for (ForeignKeyInfo fk : table.getForeignKeys()) {
                String relation = String.format("%s.%s -> %s.%s", 
                        table.getName(), fk.getColumnName(), 
                        fk.getReferencedTable(), fk.getReferencedColumn());
                
                // 添加到关系列表
                tableRelations.computeIfAbsent(table.getName(), k -> new ArrayList<>())
                        .add(relation);
            }
        }
        
        // 输出表关系
        for (String tableName : tableRelations.keySet()) {
            sb.append(String.format("\n表 %s 的关系：\n", tableName));
            for (String relation : tableRelations.get(tableName)) {
                sb.append(String.format("- %s\n", relation));
            }
        }

        return sb.toString();
    }

    /**
     * 检测数据库结构是否变更，用于实现一致性保障
     * @param connectionId 连接ID
     * @param cachedDescription 缓存的结构描述
     * @return 如果结构有变化，返回true
     */
    public boolean isSchemaChanged(Long connectionId, String cachedDescription) {
        try {
            // 获取当前数据库结构
            JdbcTemplate jdbcTemplate;
            String databaseName;
            
            if (connectionId == 0L) {
                // 使用默认连接
                jdbcTemplate = defaultJdbcTemplate;
                databaseName = defaultDatabaseName;
            } else {
                // 获取连接信息
                DbConnection connection = dbConnectionService.getConnectionByIdForAdmin(connectionId);
                if (connection == null) {
                    log.warn("检测数据库结构变更失败: 连接ID={} 不存在", connectionId);
                    return false;
                }
                
                jdbcTemplate = getJdbcTemplate(connection);
                databaseName = connection.getDatabaseName();
            }
            
            // 仅获取表名和主键信息进行快速比较
            List<String> tableNames = getTableNames(jdbcTemplate, databaseName);
            String quickHash = String.join(",", tableNames);
            
            // 从缓存中提取表名
            List<String> cachedTableNames = extractTableNames(cachedDescription);
            String cachedHash = String.join(",", cachedTableNames);
            
            // 如果表名清单不同，则结构有变化
            if (!quickHash.equals(cachedHash)) {
                log.info("数据库表结构发生变化，连接ID={}", connectionId);
                return true;
            }
            
            // 随机抽样检查表结构变更
            if (tableNames.size() > 0) {
                // 随机选择最多3个表进行详细检查
                int sampleSize = Math.min(3, tableNames.size());
                Random random = new Random();
                for (int i = 0; i < sampleSize; i++) {
                    String tableName = tableNames.get(random.nextInt(tableNames.size()));
                    if (isTableChanged(jdbcTemplate, databaseName, tableName, cachedDescription)) {
                        log.info("表{}结构发生变化，连接ID={}", tableName, connectionId);
                        return true;
                    }
                }
            }
            
            return false;
        } catch (Exception e) {
            log.warn("检测数据库结构变更时发生错误: {}", e.getMessage());
            return false;
        }
    }

    /**
     * 获取数据库所有表名
     */
    private List<String> getTableNames(JdbcTemplate jdbcTemplate, String databaseName) {
        String sql = "SELECT table_name FROM information_schema.tables " +
                "WHERE table_schema = ? AND table_type = 'BASE TABLE'";
        return jdbcTemplate.queryForList(sql, String.class, databaseName);
    }

    /**
     * 从缓存描述中提取表名
     */
    private List<String> extractTableNames(String description) {
        List<String> tableNames = new ArrayList<>();
        if (description == null || description.isEmpty()) {
            return tableNames;
        }
        
        // 使用正则表达式提取表名
        Pattern pattern = Pattern.compile("表名：([^\\（\\(\\n]+)");
        Matcher matcher = pattern.matcher(description);
        
        while (matcher.find()) {
            tableNames.add(matcher.group(1).trim());
        }
        
        return tableNames;
    }

    /**
     * 检查单个表结构是否变更
     */
    private boolean isTableChanged(JdbcTemplate jdbcTemplate, String databaseName, 
                               String tableName, String cachedDescription) {
        // 获取表的列信息
        List<String> columnNames = getColumnNames(jdbcTemplate, databaseName, tableName);
        String columnsHash = String.join(",", columnNames);
        
        // 从缓存描述中提取该表的列信息
        List<String> cachedColumnNames = extractTableColumnNames(cachedDescription, tableName);
        String cachedColumnsHash = String.join(",", cachedColumnNames);
        
        return !columnsHash.equals(cachedColumnsHash);
    }

    /**
     * 获取表所有列名
     */
    private List<String> getColumnNames(JdbcTemplate jdbcTemplate, String databaseName, String tableName) {
        String sql = "SELECT column_name FROM information_schema.columns " +
                "WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position";
        return jdbcTemplate.queryForList(sql, String.class, databaseName, tableName);
    }

    /**
     * 从缓存描述中提取指定表的列名
     */
    private List<String> extractTableColumnNames(String description, String tableName) {
        List<String> columnNames = new ArrayList<>();
        
        // 使用正则表达式提取表及其列
        String tablePattern = "表名：" + Pattern.quote(tableName) + ".*?字段列表：\\n(.*?)(?=\\n\\n|$)";
        Pattern pattern = Pattern.compile(tablePattern, Pattern.DOTALL);
        Matcher matcher = pattern.matcher(description);
        
        if (matcher.find()) {
            String columnsSection = matcher.group(1);
            // 提取列名
            Pattern columnPattern = Pattern.compile("- ([^ ]+) \\(");
            Matcher columnMatcher = columnPattern.matcher(columnsSection);
            
            while (columnMatcher.find()) {
                columnNames.add(columnMatcher.group(1).trim());
            }
        }
        
        return columnNames;
    }
} 